from django.shortcuts import render
from django.http import JsonResponse
import pymysql
import base64
import datetime
import uuid
import hashlib
connection = pymysql.connect(host='127.0.0.1', user='root', password='777271ll',db='schooldata')

# Create your views here.
def get_md5(text):
    # 创建MD5对象
    md5 = hashlib.md5()
    
    # 更新要加密的文本
    md5.update(text.encode('utf-8'))
    
    # 获取加密后的结果（16进制表示）
    result = md5.hexdigest()
    
    return result

def login(request):
        id=request.GET['id']
        password=request.GET['password']
        pw=base64.b64decode(password).decode('utf-8')
        print(type(pw))
        pws=pw.split(',')
        print(pws)
        pwstr=""
        for i in pws:
            pwstr+=chr(int(i))
        print(pwstr)
        pwstr=get_md5(pwstr)
        sql=f'''select * from password where id="{id}" and password="{pwstr}"'''
        print(sql)
        cursor = connection.cursor()
        cursor.execute(sql)
        result = cursor.fetchall()
        print(result)
        if len(result)==0:
            return JsonResponse({"result":"fail"})
        #查询
        role=""
        #以下为测试使用：
        if id[0:2]=='00':
            role="admin"
            name="管理员"
            department="管理员"
        elif id[0:2]=='10':
            role="teacher"
            sql=f'''select t.tname,d.dept_name from teacher t
            join department d on t.dept_id=d.dept_id
            where staff_id={id}'''
            cursor=connection.cursor()
            cursor.execute(sql)
            result=cursor.fetchone()
            name=result[0]
            department=result[1]
        else:
            role="student"
            sql=f'''select s.sname,d.dept_name from student s 
            join department d on s.dept_id=d.dept_id
            where student_id={id}'''
            cursor=connection.cursor()
            cursor.execute(sql)
            result=cursor.fetchone()
            name=result[0]
            department=result[1]

        userinfo={"id":id,"name":name,"department":department,"role":role}

        response=JsonResponse({"result":"success","data":userinfo})
        return response


def student_course_query(request):
        course_id=request.GET['course_id']
        course_name=request.GET['course_name']
        teacher_name=request.GET['teacher_name']
        #template
        cur=connection.cursor()
        sql='''
        select course_id,course_name,teacher_name,staff_id,class_time,credit,credit_hours from class_teacher_course_view 
        '''
        if(course_id!="" or course_name!="" or teacher_name!=""):
            sql+='where '
            if(course_id!=""):
                sql+=f"course_id like '%{course_id}%' and "
            if(course_name!=""):
                sql+=f"course_name like '%{course_name}%' and "
            if(teacher_name!=""):
                sql+=f"teacher_name like '%{teacher_name}%' and "
            sql=sql[:-4]

        print(sql)

        headers=["course_id","course_name","teacher_name","staff_id","class_time","credit","credit_hours"]

        cur.execute(sql)
        results=cur.fetchall()
        print(2)
        res=[]

        for item in results:
            itemdic={}
            for i in range(7):
                itemdic[headers[i]]=item[i]
            res.append(itemdic)


        print(res)

        resp={"data":res}

        connection.commit()#important
        cur.close()
        response=JsonResponse(resp)
        return response
    
def student_select_course(request):
        #student_id, semester, course_id, staff_id
        student_id=request.GET['student_id']
        course_id=request.GET['course_id']
        semester=request.GET['semester']
        staff_id=request.GET['staff_id']
        class_time=request.GET['class_time']
        cur=connection.cursor()
        sql=f'''
        select * from course_selection
            where student_id={student_id} and course_id={course_id} and semester='{semester}'
        '''
        print(sql)
        cur.execute(sql)
        results=cur.fetchall()
        print(results)
        sql=f'''
        select * from course_selection cs
            join class cl on cs.course_id=cl.course_id and cs.staff_id=cl.staff_id
	        where student_id={student_id} and class_time="{class_time}"
        '''
        cur.execute(sql)
        results1=cur.fetchall()
        print(results1)
        if (len(results)==0 and len(results1)==0):
            sql='''
            insert into course_selection(student_id, semester, course_id, staff_id)
            values(%s,%s,%s,%s)
            '''
            cur.execute(sql,(student_id, semester, course_id, staff_id))
            connection.commit()#important
            cur.close()
            return JsonResponse({'code':200,'msg':"选课成功"})
        elif (len(results)>0):
            cur.close()
            return JsonResponse({'code':201,'msg':"课程已选"})
        elif (len(results1)>0):
            cur.close()
            return JsonResponse({'code':202,'msg':"时间冲突"})
        
def student_selected_course_query(request):
        student_id=request.GET['student_id']
        cur=connection.cursor()
        sql=f'''
        select * from course_selection_view
            where student_id={student_id}
        '''
        cur.execute(sql)
        results=cur.fetchall()
        print(results)
        headers=["course_id","staff_id","student_id","class_time","teacher_name","course_name","credit","credit_hours"]

        res=[]

        for item in results:
            itemdic={}
            for i in range(len(headers)):
                itemdic[headers[i]]=item[i]
            res.append(itemdic)


        print(res)

        resp={"data":res}

        cur.close()
        return JsonResponse({'code':200,'msg':"查询成功",'data':resp})

def student_selected_course_delete(request):
    student_id=request.GET['student_id']
    course_id=request.GET['course_id']
    cur=connection.cursor()
    sql=f'''
    delete from course_selection where student_id={student_id} and course_id={course_id}
    '''
    cur.execute(sql)
    connection.commit()
    cur.close()
    return JsonResponse({'code':200,'msg':"删除成功"})

def student_score_query(request):
    student_id=request.GET['student_id']
    cur=connection.cursor()
    sql=f'''
    select * from student_score_view
        where student_id={student_id}
    '''
    cur.execute(sql)
    results=cur.fetchall()
    print(results)
    headers=["course_id","staff_id","student_id","teacher_name","course_name","credit","normal_score","test_score","total_score"]
    
    res=[]
    for item in results:
        itemdic={}
        for i in range(len(headers)):
            itemdic[headers[i]]=item[i]
        res.append(itemdic)
    print(res)

    resp={"data":res}

    cur.close()
    return JsonResponse({'code':200,'msg':"查询成功",'data':resp})


def teacher_query_course(request):
    staff_id=request.GET['staff_id']
    cur=connection.cursor()
    sql=f'''
    select co.course_name,cl.class_time from class cl
    join course co on cl.course_id=co.course_id
    where staff_id={staff_id}
    '''
    cur.execute(sql)
    results=cur.fetchall()
    print(results)
    res=list(results)
    return JsonResponse({'code':200,'msg':"查询成功",'data':res})

def teacher_query_student_by_course(request):
    course_name=request.GET['course_name']
    staff_id=request.GET['staff_id']
    cur=connection.cursor()
    sql=f'''
    select 
        s.student_id,
        s.sname,
        s.grade,
        s.sex,
        s.mobile_phone,
        d.dept_name
    from course_selection cs
    join student s on cs.student_id=s.student_id
    join department d on s.dept_id=d.dept_id
    join course co on cs.course_id=co.course_id
    where co.course_name='{course_name}' and cs.staff_id={staff_id}
    '''
    cur.execute(sql)
    results=cur.fetchall()
    print(results)
    headers=["student_id","sname","grade","sex","mobile_phone","dept_name"]
    res=[]
    for item in results:
        itemdic={}
        for i in range(len(headers)):
            itemdic[headers[i]]=item[i]
        res.append(itemdic)
    print(res)
    return JsonResponse({'code':200,'msg':"查询成功",'data':res})

def teacher_search_student(request):
    student_id=request.GET['student_id']
    cur=connection.cursor()
    sql=f'''
    select 
        s.student_id,
        s.sname,
        s.grade,
        s.sex,
        s.mobile_phone,
        d.dept_name
    from student s
    join department d on s.dept_id=d.dept_id
    where s.student_id={student_id}'''
    cur.execute(sql)
    results=cur.fetchall()
    print(results)
    headers=["student_id","sname","grade","sex","mobile_phone","dept_name"]
    res=""
    for item in results:
        for i in range(len(headers)):
            res+=f'{item[i]}  '
    print(res)
    return JsonResponse({'code':200,'msg':"查询成功",'data':res})

def teacher_add_student_to_class(request):
    student_id=request.GET['student_id']
    course_name=request.GET['course_name']
    staff_id=request.GET['staff_id']
    class_time=request.GET['class_time']
    semester=request.GET['semester']
    cur=connection.cursor()
    sql=f'''
    select sname from student
    where student_id='{student_id}'
    '''
    cur.execute(sql)
    results=cur.fetchall()
    print(results[0][0])
    sname=results[0][0]
    sql=f'''
    select course_id from course
    where course_name='{course_name}'
    '''
    cur.execute(sql)
    results=cur.fetchall()
    print(results[0][0])
    course_id=results[0][0]


    sql=f'''
    select * from course_selection 
    where student_id={student_id} and course_id={course_id}
    '''
    cur.execute(sql)
    results=cur.fetchall()
    if len(results)>0:  
        return JsonResponse({'code':201,'msg':"该学生已经选过此课程"})
         

    sql=f'''
    INSERT INTO course_selection (student_id, semester, course_id, staff_id, normal_score, test_score, total_score)
    VALUES 
    ({student_id}, '{semester}', '{course_id}', '{staff_id}', null, null, null);
    '''
    cur.execute(sql)
    connection.commit()
    return JsonResponse({'code':200,'msg':"添加成功"})

def teacher_delete_student_from_class(request):
    student_id=request.GET['student_id']
    course_name=request.GET['course_name']
    semester=request.GET['semester']
    cur=connection.cursor()
    sql=f'''
    select course_id from course
    where course_name='{course_name}'
    '''
    cur.execute(sql)
    results=cur.fetchall()
    print(results[0][0])
    course_id=results[0][0]
    sql=f'''
    delete from course_selection where student_id={student_id} and course_id={course_id} and semester='{semester}'
    '''
    cur.execute(sql)
    connection.commit()
    return JsonResponse({'code':200,'msg':"删除成功"})

def teacher_query_student_for_score(request):
    course_name=request.GET['course_name']
    staff_id=request.GET['staff_id']
    cur=connection.cursor()
    sql=f'''
    select 
        s.student_id,
        s.sname,
        s.grade,
        cs.normal_score,
        cs.test_score,
        cs.total_score
    from course_selection cs
    join student s on cs.student_id=s.student_id
    join course co on cs.course_id=co.course_id
    where co.course_name='{course_name}' and cs.staff_id={staff_id}
    '''
    cur.execute(sql)
    results=cur.fetchall()
    print(results)
    headers=["student_id","sname","grade","normal_score","test_score","total_score"]
    res=[]
    for item in results:
        itemdic={}
        for i in range(len(headers)):
            itemdic[headers[i]]=item[i]
        res.append(itemdic)
    print(res)
    return JsonResponse({'code':200,'msg':"查询成功",'data':res})

def teacher_mod_score(request):
    course_name=request.GET['course_name']
    staff_id=request.GET['staff_id']
    student_id=request.GET['student_id']
    normal_score=request.GET['normal_score']
    test_score=request.GET['test_score']
    total_score=request.GET['total_score']
    cur=connection.cursor()
    sql=f'''
    select course_id from course
    where course_name='{course_name}'
    '''
    cur.execute(sql)
    course_id=cur.fetchone()[0]
    sql=f'''
    update course_selection
    set
        normal_score={normal_score},
        test_score={test_score},
        total_score={total_score}
    where
        course_id={course_id} and staff_id={staff_id} and student_id={student_id}
    '''
    cur.execute(sql)
    connection.commit()
    return JsonResponse({'code':200,'msg':"修改成功"})

def teacher_query_signin(request):
    staff_id=request.GET['staff_id']
    course_name=request.GET['course_name']
    cur=connection.cursor()
    sql=f'''
    select course_id from course
    where course_name='{course_name}'
    '''
    cur.execute(sql)
    course_id=cur.fetchone()[0]
    sql=f'''
    select * from signin
    where staff_id={staff_id} and course_id={course_id}
    '''
    cur.execute(sql)
    results=cur.fetchall()
    headers=["signin_id","course_id","staff_id","release_date","cnt"]
    res=[]
    for item in results:
        itemdic={}
        for i in range(len(headers)):
            itemdic[headers[i]]=item[i]
        res.append(itemdic)
    return JsonResponse({'code':200,'msg':"查询成功",'data':res})

def teacher_query_signin_detail(request):
    signin_id=request.GET['signin_id']
    cur=connection.cursor()
    sql=f'''
    select s.sname from signin_s ss
    join student s on ss.student_id=s.student_id
    where signin_id='{signin_id}'
    '''
    cur.execute(sql)
    results=cur.fetchall()
    print(results)
    sql=f'''
    select s.sname from course_selection cs
    join student s on cs.student_id=s.student_id
    join signin si on cs.staff_id=si.staff_id and cs.course_id=si.course_id
    where si.signin_id='{signin_id}'
    '''
    cur.execute(sql)
    students=cur.fetchall()
    print(students)
    signed=""
    for stu in results:
        signed+=stu[0]+","
    unsigned=""
    for stu in students:
        if stu not in results:
            unsigned+=stu[0]+","
    signed=signed[:-1]
    unsigned=unsigned[:-1]
    return JsonResponse({'code':200,'msg':"查询成功",'signed':signed,'unsigned':unsigned})

def teacher_signin_release(request):
    staff_id=request.GET['staff_id']
    course_name=request.GET['course_name']
    cur=connection.cursor()
    sql=f'''
    select course_id from course
    where course_name='{course_name}'
    '''
    cur.execute(sql)
    course_id=cur.fetchone()[0]
    #获取当前日期 YYYY-MM-DD
    now=datetime.datetime.now()
    now=now.strftime('%Y-%m-%d')
    print(now)
    signin_id=str(uuid.uuid1())
    sql=f'''
    insert signin 
    value('{signin_id}','{course_id}','{staff_id}','{now}',0)
    '''
    cur.execute(sql)
    connection.commit()
    return JsonResponse({'code':200,'msg':"发布成功"})

def student_signin_query(request):
    student_id=request.GET['student_id']
    cur=connection.cursor()
    sql=f'''
    select 
        si.signin_id,
        co.course_name
    from signin si
    join class cl on si.course_id=cl.course_id and si.staff_id=cl.staff_id
    join course co on co.course_id=cl.course_id 
    join course_selection cs on co.course_id=cs.course_id and cs.staff_id=cl.staff_id
    where cs.student_id='{student_id}'
    '''
    cur.execute(sql)
    results=cur.fetchall()
    headers=['signin_id','course_name']
    res=[]
    for row in results:
        itemdic={}
        for i in range(len(headers)):
            itemdic[headers[i]]=row[i]
        sql=f'''
            select * from signin_s
            where signin_id='{itemdic['signin_id']}' and student_id={student_id}
            '''
        cur.execute(sql)
        tmp=cur.fetchall()
        if len(tmp)==0:
            res.append(itemdic)
    
    return JsonResponse({'code':200,'msg':"查询成功",'results':res})

def student_signin(request):
    student_id=request.GET['student_id']
    signin_id=request.GET['signin_id']
    cur=connection.cursor()
    sql=f'''
    insert signin_s
    value('{signin_id}',{student_id})
    '''
    cur.execute(sql)
    sql=f'''
    update signin
    set
        cnt=cnt+1
    where
        signin_id='{signin_id}'
    '''
    cur.execute(sql)
    connection.commit()
    return JsonResponse({'code':200,'msg':"签到成功"})
